﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using DTO;

namespace DAO
{
    public class TaiKhoan_DAO
    {
        /*
         *  Load tất cả tài khoản active cho datagridview trong control user
         */
        public static DataTable loadDSTatCaTaiKhoan()
        {
                SqlConnection cnn = dbConnection.hamKetNoi();
                SqlCommand cmd = new SqlCommand("spTatCaTaiKhoan", cnn);
                cmd.CommandType = CommandType.StoredProcedure;
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataTable dtb = new DataTable();
                da.Fill(dtb);
                return dtb;
        }
        // Them moi tai khoan vao database
        public static void themTaiKhoanMoi(TaiKhoan_DTO TaiKhoan)
        {
            SqlConnection cnn = dbConnection.hamKetNoi();
            SqlCommand cmd = new SqlCommand("spThemTaiKhoan", cnn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@userID", SqlDbType.NChar, 10);
            cmd.Parameters.Add("@username", SqlDbType.NChar, 15);
            cmd.Parameters.Add("@password", SqlDbType.NText);
            cmd.Parameters.Add("@email", SqlDbType.NVarChar, 50);
            // GÁN GIÁ TRỊ
            cmd.Parameters["@userID"].Value = TaiKhoan.userID;
            cmd.Parameters["@username"].Value = TaiKhoan.username;
            cmd.Parameters["@password"].Value = TaiKhoan.password;
            cmd.Parameters["@email"].Value = TaiKhoan.email;
            //
            if (cnn.State == ConnectionState.Open)
                cnn.Close();
            cnn.Open();
            cmd.ExecuteNonQuery();
            cnn.Close();
        }
        // chinh sua tai khoan vao database
        public static void suaThongTinTaiKhoan(TaiKhoan_DTO TaiKhoan)
        {
            SqlConnection cnn = dbConnection.hamKetNoi();
            SqlCommand cmd = new SqlCommand("spSuaThongTinTaiKhoan", cnn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@userID", SqlDbType.NChar, 10);
            cmd.Parameters.Add("@username", SqlDbType.NChar, 15);
            cmd.Parameters.Add("@email", SqlDbType.NVarChar, 50);
            // GÁN GIÁ TRỊ
            cmd.Parameters["@userID"].Value = TaiKhoan.userID;
            cmd.Parameters["@username"].Value = TaiKhoan.username;
            cmd.Parameters["@email"].Value = TaiKhoan.email;
            //
            if (cnn.State == ConnectionState.Open)
                cnn.Close();
            cnn.Open();
            cmd.ExecuteNonQuery();
            cnn.Close();
        }
        // doi mat khau tai khoan
        public static void doiMatKhauTaiKhoan(string userId, string password)
        {
            SqlConnection cnn = dbConnection.hamKetNoi();
            SqlCommand cmd = new SqlCommand("spDoiMatKhauTaiKhoan", cnn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@userID", SqlDbType.NChar, 10);
            cmd.Parameters.Add("@password", SqlDbType.NText);
            // GÁN GIÁ TRỊ
            cmd.Parameters["@userID"].Value = userId;
            cmd.Parameters["@password"].Value = password;
            //
            if (cnn.State == ConnectionState.Open)
                cnn.Close();
            cnn.Open();
            cmd.ExecuteNonQuery();
            cnn.Close();
        }
        // xoa tai khoan 
        public static void xoaMotTaiKhoan(string matk)
        {
            SqlConnection cnn = dbConnection.hamKetNoi();
            SqlCommand cmd = new SqlCommand("spXoaMotTaiKhoan", cnn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@userID", SqlDbType.NChar, 10);
            cmd.Parameters["@userID"].Value = matk;
            //
            if (cnn.State == ConnectionState.Open)
                cnn.Close();
            cnn.Open();
            cmd.ExecuteNonQuery();
            cnn.Close();
        }
        // lay thong tin mot tai khoan
        public static DataTable layThongTinTaiKhoan(string matk)
        {
            SqlConnection cnn = dbConnection.hamKetNoi();
            SqlCommand cmd = new SqlCommand("spLayThongTinTaiKhoan", cnn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@userID", SqlDbType.NChar, 10);
            cmd.Parameters["@userID"].Value = matk;
            //
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dtb = new DataTable();
            da.Fill(dtb);
            return dtb;
        }
        // kiem tra xem ma tai khoan da ton tai chua
        public static bool kiemTraMaTK(string maMH)
        {
            int exist = 0;
            SqlConnection cnn = dbConnection.hamKetNoi();
            SqlCommand cmd = new SqlCommand("spKiemTraMaTK", cnn);
            cmd.CommandType = CommandType.StoredProcedure;
            //
            cmd.Parameters.Add("@userID", SqlDbType.NChar, 10);
            //
            cmd.Parameters["@userID"].Value = maMH;
            //
            if (cnn.State == ConnectionState.Open)
                cnn.Close();
            cnn.Open();
            exist = (int)cmd.ExecuteScalar();
            cnn.Close();
            if (exist > 0)
                return true;
            return false;
        }
        public static bool kiemTraTenTaiKhoan(string tentk)
        {
            int exist = 0;
            SqlConnection cnn = dbConnection.hamKetNoi();
            SqlCommand cmd = new SqlCommand("spKiemTraTenTK", cnn);
            cmd.CommandType = CommandType.StoredProcedure;
            //
            cmd.Parameters.Add("@username", SqlDbType.NChar, 10);
            //
            cmd.Parameters["@username"].Value = tentk;
            //
            if (cnn.State == ConnectionState.Open)
                cnn.Close();
            cnn.Open();
            exist = (int)cmd.ExecuteScalar();
            cnn.Close();
            if (exist > 0)
                return true;
            return false;
        }
    }
}
